SET SERVEROUTPUT ON SIZE 1000000;
set verify off;

DECLARE
	l_total_extracted		NUMBER;
	l_total_extracted_prg	NUMBER;
	l_total_imported		NUMBER;		
	l_total_imported_prg	NUMBER;	
	l_func_id				VARCHAR2(100):='CAPAPREC_PAY_FORMAT';
	
BEGIN
	SELECT COUNT(1) 
	INTO l_total_extracted
	FROM
		AP_CHECK_FORMATS	F,
		AP_PAYMENT_PROGRAMS	P1,
		AP_PAYMENT_PROGRAMS	P2,
		AP_PAYMENT_PROGRAMS	P3
	WHERE	SUBSTR(F.Name,1,4)				=	UPPER('&1')
	AND		F.Build_Payments_Program_ID		=	P1.Program_ID(+)
	AND		F.Format_Payments_Program_ID	=	P2.Program_ID(+)
	AND		F.Remittance_Advice_Program_ID	=	P3.Program_ID(+);


	SELECT COUNT(1) 
	INTO l_total_extracted_prg
	FROM
		AP_PAYMENT_PROGRAMS p
	WHERE	SUBSTR(P.Friendly_Name,1,4) = UPPER('&1');
	
		
	SELECT COUNT(1) 
	INTO l_total_imported
	FROM CRST_AP_PAY_FORMAT;
	
	SELECT COUNT(1) 
	INTO l_total_imported_prg
	FROM CRST_AP_PAY_PROGRAMS;

	dbms_output.put_line('Function: '||l_func_id);		
	dbms_output.put_line('Total Extracted: '||TO_CHAR(l_total_extracted)) ;
	dbms_output.put_line('Total Imported:  '||TO_CHAR(l_total_imported));	
	IF l_total_extracted <> l_total_imported THEN
		dbms_output.put_line('Error: No. of Records do not match!');
	END IF;		
				
	dbms_output.put_line('Total Extracted - Program: '||TO_CHAR(l_total_extracted_prg)) ;
	dbms_output.put_line('Total Imported - Program:  '||TO_CHAR(l_total_imported_prg));	
	IF l_total_extracted_prg <> l_total_imported_prg THEN
		dbms_output.put_line('Error: No. of Records do not match!');
	END IF;				
EXCEPTION
	WHEN OTHERS THEN	
		dbms_output.put_line('Function Error: '||l_func_id);	

END;
/